package com.minisoft.pool;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUnit;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.Page;
import cn.hutool.db.PageResult;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.minisoft.utils.HttpClientService;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;

import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author JueSu
 * @description: TODO
 * @date 2022/8/15 17:56
 */
@Slf4j
public class PoolTest {

//    public static String DB_CENTER = "center-dev";
//    public static String DB_OUTER = "outer-dev";

    public static String DB_CENTER = "center-pro";
    public static String DB_OUTER = "outer-pro";

    HttpClientService httpClientService = new HttpClientService();

    /**
     * 通过ftx_clue_info，ftx_clue_sync_pool找出已上报的 手机，案场和时间，
     * 根据条件去来源中查找对应类型的渠道记录，找到一条，就获取，否则下一步
     * 通过渠道ID看存在哪个分配记录表，有就选它，没有则选最新的
     */
    @Test
    public void testImportOuterToPoolFTX() throws SQLException {
        Page page = new Page(0, 500);
        PageResult<Entity> pageResult = null;
        int source = 10,source_type = 19;
        while (pageResult == null || !pageResult.isLast()) {
            if (pageResult != null) {
                page.setPageNumber(page.getPageNumber() + 1);
            }
            pageResult = Db.use(DB_OUTER).page("select c.* from ftx_clue_info c,ftx_clue_sync_pool p where c.clue_id = p.clue_id and p.create_date > '2022-11-14'",page);
            List<String> mobileList = pageResult.stream().map(item -> item.getStr("clue_tel")).collect(Collectors.toList());
            List<Entity> cpList = Db.use(DB_CENTER).findBy("customer_potential","mobile",mobileList);//通过in查询
            Map<String, Long> cpIdMap = cpList.stream().collect(Collectors.toMap(item -> item.getStr("mobile"), item -> item.getLong("id")));
            for (Entity clue : pageResult) {
                Long customer_potential_id = cpIdMap.get(clue.getStr("clue_tel"));
                processChannel(clue, source, source_type, customer_potential_id);
            }
        }
    }

    /**
     * 先找到已上报的潜客ID，
     * 去来源中查找对应类型的渠道记录，通过这个ID
     * 通过渠道ID看存在哪个分配记录表，有就选它，没有则选最新的
     * 如果失败查看cp_allot_fail_user的渠道ID是否有对应的错误信息
     */
    @Test
    public void testImportOuterToPoolTX() throws SQLException {
        List<Entity> txList = Db.use(DB_OUTER).query("select * from tencent_ad_clue_info i where i.pushed_flag = 1 and i.create_date > '2022-11-08 17:45:01'");
        log.info("tx clue size {}",txList.size());
        for (Entity clue : txList) {
            int source = 12,source_type = 21;
            Long customer_potential_id = clue.getLong("customer_potential_id");
            processChannel(clue, source, source_type,customer_potential_id);
        }
    }

    private void processChannel(Entity clue, int source, int source_type, Long customer_potential_id) throws SQLException {
        List<Entity> channelList = Db.use(DB_CENTER).query("select * from customer_potential_channel c where c.source = ? and source_type = ? and c.customer_potential_id = ? order by c.id desc", source,source_type,customer_potential_id);
        //手动导入记录，以待排除
        String mobile = ObjectUtil.defaultIfNull(clue.getStr("leads_telephone"), clue.getStr("clue_tel"));
        List<Entity> importList = Db.use(DB_CENTER).query("select * from cp_import_user u where u.source = ? and u.source_type = ? and mobile = ? and batch_id is not null ", source, source_type,mobile);
        int importTotal = 0;
        for (Entity channel : channelList) {
            Date channel_create_time = channel.getDate("create_time");
            boolean in = false;
            for (Entity importEntity : importList) {
                //如果手动导入存在时间相差无几的，则认为是手工导入的
                Date import_create_time = importEntity.getDate("create_time");
                long between = DateUtil.between(channel_create_time, import_create_time, DateUnit.MS, true);
                in = between < 1000;//相差1秒内
                if (in) {
                    log.info("存在手动导入记录，跳过 {}", clue);
                    break;
                }
            }
            if (!in) {//不存在手动导入的，则增加
                importTotal++;
                importByChannel(channel, clue);
            }
        }
        if (importTotal > 1) {
            log.info("有多次自动导入线索 {}", clue);
        }
    }

    private void importByChannel(Entity channel, Entity clue) throws SQLException {
        String msg = null;
        Entity fail = Db.use(DB_CENTER).queryOne("select * from cp_allot_fail_user u where u.customer_potential_channel_id = ?", channel.getLong("id"));
        if (fail != null) {
            msg = fail.getStr("error_msg");
        }

        Entity cp_import_user = Entity.create("cp_import_user");
        boolean isTx = clue.getLong("customer_potential_id") != null;
        cp_import_user.set("city",channel.getStr("city_name"))
                .set("sale_team_name",channel.getStr("sale_team_name"))
                .set("acquisition_time", ObjectUtil.defaultIfNull(clue.getDate("leads_create_time"),clue.getDate("dispath_time")))
                .set("source",isTx ? 12 : 10)
                .set("source_type",isTx ? 21: 19)
                .set("status",StrUtil.isBlank(msg) ? 3 : 2) //2分配失败；3分配成功
                .set("data_source",1)
                .set("mobile",ObjectUtil.defaultIfNull(clue.getStr("leads_telephone"),clue.getStr("clue_tel")))
                .set("nickname",isTx ? StrUtil.blankToDefault(clue.getStr("leads_name"),"腾讯广告用户") : clue.getStr("clue_name"))
                .set("sale_mobile",clue.getStr("saler_tel"))
                .set("sale_name",clue.getStr("saler_name"))
                .set("adgroup_name",clue.getStr("adgroup_name"))
                .set("adgroup_id",clue.getStr("adgroup_id"))
                .set("campaign_name",clue.getStr("campaign_name"))
                .set("campaign_id",clue.getStr("campaign_id"))
                .set("customer_potential_channel_id",channel.getLong("id"))
                .set("msg",msg);
        Db.use(DB_CENTER).insert(cp_import_user);
    }


    /**
     * 失效分配数据
     * @throws SQLException
     */
    @Test
    public void invalidAllocationData() throws SQLException {
        ExcelReader reader = ExcelUtil.getReader("D:\\temp\\潜客重新分配源数据.xlsx");
        reader.setSheet(0);
        List<Map<String, Object>> list = reader.readAll();

        int allocationType = 2,cityId = 1119006;
        for (Map<String, Object> map : list) {
            String sale_team_id = map.get("sale_team_id").toString();
            String details_id = map.get("details_id").toString();
            System.out.println(sale_team_id + " " + details_id);
            Map<String, String> formMap = new HashMap<>();
            formMap.put("allocationType", String.valueOf(allocationType));
            formMap.put("cityId", String.valueOf(cityId));
            formMap.put("saleTeamId", sale_team_id);
            formMap.put("detailsId", details_id);
            httpClientService.doPost("http://customer-pool.paas.huafagroup.com/public/customer/invalidAllocationData",formMap,null);
        }
    }

}
